<?
class htdb {

  	function view_content($page_id, $db) {
   		$sql = "SELECT page_id, page_name, url_name, page_content,DATE_FORMAT(modified_dttm,'%m/%d/%Y') AS modified_dttm
				FROM pages
				WHERE page_id = $page_id";
		$query = $db->query($sql);
		$content_array = $db->fetch_array($query);

		return $content_array;
  	}

	function update_content($noise, $page_id, $member_id, $db) {
		$sql = "UPDATE pages
                SET page_content = '$noise',
                	member_id = $member_id,
                	modified_dttm = now()
              	WHERE page_id = $page_id";
 		$query = $db->query($sql);
		return $query;
	}

	function view_all_images($memorial_id, $db){

		$sql = "SELECT i.memorial_id,i.image_id, i.filename, i.width, i.height, DATE_FORMAT(i.posted_dttm,'%m/%d/%Y') AS posted_dttm, i.uniqid, i.image_descr AS image_description
             	FROM mem_images i
             	WHERE memorial_id = $memorial_id
             	ORDER BY i.image_id DESC";
     	$query = $db->query($sql);
     	$images_array = array();

     	while($r = mysql_fetch_array($query)) {
       		array_push($images_array,$r);
     	}

     	return $images_array;
	}

	function add_image($image_filename,$width,$height,$descr,$memorial_id,$uniqid,$db){
      $sql = "INSERT INTO mem_images (filename,width,height,image_descr,memorial_id,uniqid,posted_dttm)
      		  VALUES ('$image_filename',$width,$height,'$descr',$memorial_id,'$uniqid',now())";
	  $query = $db->query($sql);
      $image_id = $db->last_inserted();

      return $image_id;
	}

   function delete_image($image_id,$db) {
	  $sql = "DELETE
			  FROM mem_images
			  WHERE image_id = $image_id";
		$query = $db->query($sql);

	  return $query;
	}

  	function view_active_details($page_id,$db) {
   		$sql = "SELECT p.detail_id,  p.page_id, p.detail_title, p.brief_descr, p.content, p.active_ind, DATE_FORMAT(p.modified_dttm,'%m/%d/%Y') AS modified_dttm, m.fname, m.lname AS modified_by
				FROM page_details p
				INNER JOIN members m ON m.member_id = p.member_id
				WHERE page_id = $page_id
				AND active_ind = '1'
				ORDER BY create_dttm desc";
        $query = $db->query($sql);
	    $details_array = array();

	    while($r = mysql_fetch_array($query)) {
	        array_push($details_array,$r);
	    }

	      return $details_array;
	}

	function view_members_count($db) {
		$sql = "SELECT COUNT(member_id) AS members_count
				FROM members
				WHERE member_id <> 1";

		  $query = $db->query($sql);
		  $r = $db->fetch_array($query);

		  $member_count = $r["members_count"];

		  return $member_count;
	}

	function view_select_members($lower_limit,$listings_per_page,$db) {

		$sql="SELECT member_id, username,  fname, lname, email, DATE_FORMAT(create_dttm,'%m/%d/%Y') AS create_dttm, DATE_FORMAT(modified_dttm,'%m/%d/%Y') AS modified_dttm, CASE WHEN active_ind = '1' THEN 'Y' ELSE 'N' END AS active_ind
			  FROM members
			  WHERE member_id <> 1
			  ORDER BY member_id desc
			  LIMIT $lower_limit,$listings_per_page";
		$query = $db->query($sql);
		$member_array = array();

			  while($r = mysql_fetch_array($query)) {
				array_push($member_array,$r);
			  }

			  return $member_array;
	}

	function view_member($member_id, $db){
   		$sql = "SELECT member_id, username, fname, lname, email, DATE_FORMAT(modified_dttm,'%m/%d/%Y') AS modified_dttm,CASE WHEN active_ind = '1' THEN 'Y' ELSE 'N' END AS active_ind
				FROM members
				WHERE member_id = $member_id";
		$query = $db->query($sql);
		$member_array = $db->fetch_array($query);

		return $member_array;
  	}

   function delete_member($member_id,$db) {
	  $sql = "DELETE
			  FROM members
			  WHERE member_id = $member_id";
		$query = $db->query($sql);

	  return $query;
	}

	function add_member($username,$password,$fname,$lname,$email,$db) {
		$sql = "INSERT INTO members (username,password,fname,lname,email,modified_dttm,create_dttm, active_ind)
				VALUES ('$username','$password','$fname','$lname','$email',now(),now(),'1')";
		$query = $db->query($sql);
		$member_id = $db->last_inserted();
		return $member_id;
	}

	function update_member($member_id,$fname,$lname,$email,$active_ind,$db){
		$sql = "UPDATE members
                SET fname = '$fname',
                    lname = '$lname',
                    email = '$email',
                    active_ind = '$active_ind',
                    modified_dttm = now()
              WHERE member_id = $member_id";

 		$query = $db->query($sql);
		return $query;
	}

  	function view_all_categories($db) {
   		$sql = "SELECT f.cat_id, f.cat_descr
				FROM food_categories f
				ORDER BY f.cat_descr";
        $query = $db->query($sql);
	      $cat_array = array();

	      while($r = mysql_fetch_array($query)) {
	        array_push($cat_array,$r);
	      }

	      return $cat_array;
	}

	function view_foods_count($search_str,$db) {
		$sql = "SELECT COUNT(food_id) AS foods_count
				FROM `foodcomp`
				WHERE food_name LIKE '%$search_str%'";
		$query = $db->query($sql);

		$r = $db->fetch_array($query);

		$foods_count = $r["foods_count"];
		return $foods_count;
	}

	function view_select_foods($lower_limit,$listings_per_page,$search_str,$db) {

		$sql="SELECT food_id, food_name
			  FROM `foodcomp`
			  WHERE food_name LIKE '%$search_str%'
			  ORDER BY food_id
			  LIMIT $lower_limit,$listings_per_page";
		$query = $db->query($sql);
		$food_array = array();

			  while($r = mysql_fetch_array($query)) {
				array_push($food_array,$r);
			  }
			  return $food_array;
	}
	function view_food($food_id, $db){
   		$sql = "SELECT f.food_id AS food_id, f.cat_id, c.cat_descr AS cat_descr, f.food_name AS food_name, f.gmwt_desc1 AS gmwt_desc1, gmwt1, gmwt_desc2, gmwt2, calories, saturated_fat, trans_fat, carbohydrates, dietary_fiber, sugar, protein, cholesterol, sodium, iron, vitc, vitb6, calcium, vita_ui
				FROM foodcomp f
				INNER JOIN food_categories c ON c.cat_id = f.cat_id
				WHERE food_id = $food_id";
		$query = $db->query($sql);
		$food_array = $db->fetch_array($query);

		return $food_array;
  	}

	function add_food($food_name,$cat_id,$gmwt_desc1,$gmwt1,$gmwt_desc2,$gmwt2,$calories,$saturated_fat,$trans_fat,$carbohydrates,$dietary_fiber,$sugar,$protein,$cholesterol,$sodium,$iron,$vitc,$vitb6,$calcium,$vita_ui,$db){
		$sql = "INSERT INTO foodcomp (food_name,cat_id,gmwt_desc1,gmwt1,gmwt_desc2,gmwt2,calories,saturated_fat,trans_fat,carbohydrates,dietary_fiber,sugar,protein,cholesterol,sodium,iron,vitc,vitb6,calcium,vita_ui)
				VALUES ('$food_name',$cat_id,'$gmwt_desc1','$gmwt1','$gmwt_desc2','$gmwt2','$calories','$saturated_fat','$trans_fat','$carbohydrates','$dietary_fiber','$sugar','$protein','$cholesterol','$sodium','$iron','$vitc','$vitb6','$calcium','$vita_ui')";
		$query = $db->query($sql);
		$food_id = $db->last_inserted();
		return $food_id;
	}
	function update_food($food_id,$food_name,$cat_id,$gmwt_desc1,$gmwt1,$gmwt_desc2,$gmwt2,$calories,$saturated_fat,$trans_fat,$carbohydrates,$dietary_fiber,$sugar,$protein,$cholesterol,$sodium,$iron,$vitc,$vitb6,$calcium,$vita_ui,$db){
		$sql = "UPDATE foodcomp
                SET food_id = $food_id,
                    food_name = '$food_name',
                    cat_id = $cat_id,
                    gmwt_desc1 = '$gmwt_desc1',
                    gmwt1 = '$gmwt1',
                    gmwt_desc2 = '$gmwt_desc2',
                    gmwt2 = '$gmwt2',
                    calories = '$calories',
                    saturated_fat = '$saturated_fat',
                    trans_fat = '$trans_fat',
                    carbohydrates = '$carbohydrates',
                    dietary_fiber = '$dietary_fiber',
                    sugar = '$sugar',
                    protein = '$protein',
                    cholesterol = '$cholesterol',
                    sodium = '$sodium',
                    iron = '$iron',
                    vitc = '$vitc',
                    vitb6 = '$vitb6',
                    calcium = '$calcium',
                    vita_ui = '$vita_ui'
              WHERE food_id = $food_id";

 		$query = $db->query($sql);
		return $query;
	}

   function delete_food($food_id,$db) {
	  $sql = "DELETE
			  FROM foodcomp
			  WHERE food_id = $food_id";
		$query = $db->query($sql);

	  return $query;
	}
}
?>